#! /bin/bash

HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "

create database if not exists oe_dwd;
use oe_dwd;

-- 贺怡琳
-- 客户意向表
drop table if exists oe_dwd.fact_customer_relationship;
create table if not exists oe_dwd.fact_customer_relationship(
    id                          int comment '意向id',
    create_date_time            string comment '首次更新时间',
    update_date_time            string comment '最后更新时间',
    customer_id                 int comment '所属客户id',
    first_id                    int comment '第一条客户关系id',
    origin_type                 string comment '数据来源',
    itcast_school_id            int comment '校区Id',
    itcast_subject_id           int comment '学科Id',
    intention_study_type        string comment '意向学习方式',
    anticipat_signup_date       string comment '预计报名时间',
    level                       string comment '客户级别',
    creator                     string comment '创建人',
    current_creator             string comment '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                string comment '创建者姓名',
    origin_channel              string comment '来源渠道',
    process_state               string comment '处理状态',
    process_time                string comment '处理状态变动时间',
    payment_state               string comment '支付状态',
    payment_time                string comment '支付状态变动时间',
    signup_state                string comment '报名状态',
    signup_time                 string comment '报名时间',
    notice_state                string comment '通知状态',
    notice_time                 string comment '通知状态变动时间',
    itcast_clazz_id             string comment '所属ems班级id',
    itcast_clazz_time           string comment '报班时间',
    payment_url                 string comment '付款链接',
    payment_url_time            string comment '支付链接生成时间',
    ems_student_id              int comment 'ems的学生id',
    course_id                   int comment '课程ID',
    course_name                 string comment '课程名称',
    tenant                      string comment '租户',
    total_fee                   decimal(38) comment '报名费总金额',
    follow_type                 string comment '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取'
)comment '客户意向表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');


-- 员工信息表
drop table if exists oe_dwd.dim_employee;
create table if not exists oe_dwd.dim_employee(
    id                  int,
    email               string comment '公司邮箱，OA登录账号',
    real_name           string comment '员工的真实姓名',
    phone               string comment '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
    department_id       string comment 'OA中的部门编号，有负值',
    department_name     string comment 'OA中的部门名',
    remote_login        string comment '员工是否可以远程登录',
    job_number          string comment '员工工号',
    cross_school        string comment '是否有跨校区权限',
    last_login_date     string comment '最后登录日期',
    creator             int comment '创建人',
    create_date_time    string comment '创建时间',
    update_date_time    string comment '最后更新时间',
    deleted             string comment '是否被删除（禁用）',
    scrm_department_id  int comment 'SCRM内部部门id',
    leave_office        string comment '离职状态',
    leave_office_time   string comment '离职时间',
    reinstated_time     string comment '复职时间',
    superior_leaders_id int comment '上级领导ID',
    tdepart_id          int comment '直属部门',
    tenant              int,
    ems_user_name       string
)comment '员工信息表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

-- 班级信息表
drop table if exists oe_dwd.dim_itcast_clazz;
create table if not exists oe_dwd.dim_itcast_clazz(
     id                  int comment 'ems课程id(非自增)',
    create_date_time    string comment '创建时间',
    update_date_time    string comment '最后更新时间',
    deleted             string comment '是否被删除（禁用）',
    itcast_school_id    string comment 'ems校区ID',
    itcast_school_name  string comment 'ems校区名称',
    itcast_subject_id   string comment 'ems学科ID',
    itcast_subject_name string comment 'ems学科名称',
    itcast_brand        string comment 'ems品牌',
    clazz_type_state    string comment '班级类型状态',
    clazz_type_name     string comment '班级类型名称',
    teaching_mode       string comment '授课模式',
    start_time          string comment '开班时间',
    end_time            string comment '毕业时间',
    comment             string comment '备注',
    detail              string comment '详情(比如：27期)',
    uncertain           bigint comment '待定班(0:否,1:是)',
    tenant              int
)comment '班级信息表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

--员工部门表
drop table if exists oe_dwd.dim_scrm_department;
create table if not exists oe_dwd.dim_scrm_department(
    id               int comment '部门id',
    name             string comment '部门名称',
    parent_id        string comment '父部门id',
    create_date_time string comment '创建时间',
    update_date_time string comment '更新时间',
    deleted          string comment '删除标志',
    id_path          string comment '编码全路径',
    tdepart_code     string comment '直属部门',
    creator          string comment '创建者',
    depart_level     string comment '部门层级',
    depart_sign      string comment '部门标志，暂时默认1',
    depart_line      string comment '业务线，存储业务线编码',
    depart_sort      string comment '排序字段',
    disable_flag     string comment '禁用标志',
    tenant           string comment '租户id'
)comment '员工部门表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

-- 朱纯聪
-- 客户线索表
drop table if exists oe_dwd.fact_customer_clue;
create table if not exists oe_dwd.fact_customer_clue(
    id                       int comment 'ID',
    create_date_time         string comment '创建时间',
    update_date_time         string comment '最后更新时间',
    customer_id              string comment '客户id',
    customer_relationship_id string comment '客户关系id',
    session_id               string comment '七陌会话id',
    sid                      string comment '访客id',
    status                   string comment '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
    create_time              string comment '七陌创建时间',
    platform                 string comment '平台来源（pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
    s_name                   string comment '用户名称',
    seo_source               string comment '搜索来源',
    seo_keywords             string comment '关键字',
    ip                       string comment 'IP地址',
    referrer                 string comment '上级来源页面',
    from_url                 string comment '会话来源页面',
    landing_page_url         string comment '访客着陆页面',
    url_title                string comment '咨询页面title',
    to_peer                  string comment '所属技能组',
    total_msg_count          bigint comment '消息总数',
    msg_count                bigint comment '客户发送消息数',
    comment                  string comment '备注',
    end_time                 string comment '会话结束时间',
    platform_description     string comment '客户平台信息',
    browser_name             string comment '浏览器名称',
    os_info                  string comment '系统名称',
    area                     string comment '区域',
    country                  string comment '所在国家',
    province                 string comment '省',
    city                     string comment '城市',
    creator                  string comment '创建人',
    name                     string comment '客户姓名',
    idcard                   string comment '身份证号',
    phone                    string comment '手机号',
    itcast_school_id         string comment '校区Id',
    itcast_school            string comment '校区',
    itcast_subject_id        string comment '学科Id',
    itcast_subject           string comment '学科',
    wechat                   string comment '微信',
    qq                       string comment 'qq号',
    email                    string comment '邮箱',
    gender                   string comment '性别',
    origin_type              string comment '数据来源渠道',
    information_way          string comment '资讯方式',
    customer_state           string comment '当前客户状态',
    valid                    string comment '该线索是否是网资有效线索',
    anticipat_signup_date    string comment '预计报名时间',
    clue_state               string comment '线索状态',
    scrm_department_id       string comment 'SCRM内部部门id',
    origin_channel           string comment '投放渠道',
    course_id                string comment '课程ID',
    course_name              string comment '课程名称',
    zhuge_session_id         string comment '诸葛会话ID',
    shunt_employee_group_id  string comment '所属分流员工组'
)comment '客户线索表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');

-- 线索申诉表
drop table if exists oe_dwd.fact_customer_appeal;
create table if not exists oe_dwd.fact_customer_appeal(
     id                             int,
    customer_relationship_first_id string comment '第一条客户关系id',
    employee_id                    string comment '申诉人',
    employee_name                  string comment '申诉人姓名',
    employee_department_id         string comment '申诉人部门',
    employee_tdepart_id            string comment '申诉人所属部门',
    appeal_status                  string comment '申诉状态，0:待稽核 1:无效 2：有效',
    audit_id                       string comment '稽核人id',
    audit_name                     string comment '稽核人姓名',
    audit_department_id            string comment '稽核人所在部门',
    audit_department_name          string comment '稽核人部门名称',
    audit_date_time                string comment '稽核时间',
    create_date_time               string comment '创建时间（申诉时间）',
    update_date_time               string comment '更新时间',
    deleted                        string comment '删除标志位',
    tenant                         string comment '租户ID'
)comment '线索申诉表'
partitioned by (dt string comment '以实际导入/操作日期固定写入每张表')
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');


SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;


-- 客户意向表
insert into table oe_dwd.fact_customer_relationship partition (dt)
select id,
       create_date_time,
       update_date_time,
       customer_id,
       first_id,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       course_id,
       course_name,
       tenant,
       total_fee,
       follow_type,
       dt
from oe_ods.t_customer_relationship;

--员工信息表
insert into table oe_dwd.dim_employee partition (dt)
select * from oe_ods.t_employee;

-- 班级信息表
insert into table oe_dwd.dim_itcast_clazz partition (dt)
select * from oe_ods.t_itcast_clazz;

--员工部门表
insert into table oe_dwd.dim_scrm_department partition (dt)
select * from oe_ods.t_scrm_department;

-- 朱纯聪
-- 客户线索表
insert into table oe_dwd.fact_customer_clue partition (dt)
select id,
       create_date_time,
       update_date_time,
       customer_id,
       customer_relationship_id,
       session_id,
       sid,
       status,
       create_time,
       platform,
       s_name,
       seo_source,
       seo_keywords,
       ip,
       referrer,
       from_url,
       landing_page_url,
       url_title,
       to_peer,
       total_msg_count,
       msg_count,
       comment,
       end_time,
       platform_description,
       browser_name,
       os_info,
       area,
       country,
       province,
       city,
       creator,
       name,
       idcard,
       phone,
       itcast_school_id,
       itcast_school,
       itcast_subject_id,
       itcast_subject,
       wechat,
       qq,
       email,
       gender,
       origin_type,
       information_way,
       customer_state,
       valid,
       anticipat_signup_date,
       clue_state,
       scrm_department_id,
       origin_channel,
       course_id,
       course_name,
       zhuge_session_id,
       shunt_employee_group_id,
       dt
from oe_ods.t_customer_clue;

--线索申诉表
insert into table oe_dwd.fact_customer_appeal partition (dt)
select * from oe_ods.t_customer_appeal;

"
